// const app = require('express')()
// 格式化时间
var moment = require('moment')

let SnowflakeID = require('../../utils/SnowflakeID')

//导入数据库操作模块
const db = require("../../db/index.js")

// //取jwt的密钥
// const config = require('../config')
// const jwtSecretKey = config.jwtSecretKey

// //引入token 
// var vertoken = require('../utils/token/token')

/**
 * 获取学习视频列表
 * @param {*} req 
 * @param {*} res 
 */
exports.getStudyvideosList = async (req, res) => {

    console.log('获取学习视频列表')
    let sql = ''
    let tag = req.query.tag
    let title = req.query.title
    let size = parseInt(req.query.limit)
    let start = (parseInt(req.query.page) - 1) * size
    let sqlArr = []
    if (!title || title == "") {
        if (!tag || tag == "" || tag + "" == "全部") {
            sql = `SELECT COUNT(*) FROM xks_study_videos AS videos,xks_tags AS tags  WHERE videos.flag != 0 AND  tags.tag_type = "学习视频" AND videos.id=tags.c_id ;
            SELECT videos.id,videos.title,tags.tag_name AS tags,videos.description,videos.publisher,videos.create_time,videos.img_url,videos.video_url,videos.flag FROM xks_study_videos AS videos, xks_tags AS tags  WHERE videos.flag != 0 AND  tags.tag_type = "学习视频" AND videos.id=tags.c_id limit ?,?`;
            sqlArr = [start, size]
        }
        else {
            sql = `SELECT COUNT(*) FROM xks_study_videos AS videos,xks_tags AS tags  WHERE videos.flag != 0 AND  tags.tag_type = "学习视频" AND videos.id=tags.c_id AND tags.tag_name = ?;
            SELECT videos.id,videos.title,tags.tag_name AS tags,videos.description,videos.publisher,videos.create_time,videos.img_url,videos.video_url,videos.flag FROM xks_study_videos AS videos, xks_tags AS tags  WHERE videos.flag != 0 AND  tags.tag_type = "学习视频" AND videos.id=tags.c_id AND tags.tag_name = ? limit ?,?`;
            sqlArr = [tag, tag, start, size]
        }

        db.query(sql, sqlArr, (err, results) => {
            if (err) {
                return res.send({
                    code: 505,
                    msg: '错误',
                    videos: {}
                })
            }
            // 计算总页数
            var allCount = results[0][0]['COUNT(*)'];
            // console.log('results[0],', results[0])
            var allPage = parseInt(allCount) / size;
            var pageStr = allPage.toString();
            // 不能被整除
            if (pageStr.indexOf('.') > 0) {
                allPage = parseInt(pageStr.split('.')[0]) + 1;
            }
            var list = results[1];
            return res.send({
                code: 200,
                msg: '成功',
                totalPages: allPage,
                total: allCount,
                currentPage: start + 1,
                data: list
            })
        })
    } else {
        if (!tag || tag == "" || tag == "全部") {
            console.log('根据标题查,title=>', title, ',,,tag=>', tag)
            sql = `SELECT COUNT(*) FROM xks_study_videos AS videos  WHERE videos.flag != 0 AND  videos.title LIKE concat('%',?, '%');
            SELECT videos.id,videos.title,tags.tag_name AS tags,videos.description,videos.publisher,videos.create_time,videos.img_url,videos.video_url,videos.flag FROM xks_study_videos AS videos, xks_tags AS tags   WHERE videos.flag != 0 AND  videos.title LIKE concat('%',?, '%') AND tags.tag_type = "学习视频" AND videos.id=tags.c_id limit ?,?`;
            sqlArr = [title, title, start, size]
        } else {
            sql = `SELECT COUNT(*) FROM xks_study_videos AS videos,xks_tags AS tags  WHERE videos.flag != 0 AND  videos.title LIKE concat('%',?, '%') AND tags.tag_type = "学习视频" AND videos.id=tags.c_id AND tags.tag_name = ?;
            SELECT videos.id,videos.title,tags.tag_name AS tags,videos.description,videos.publisher,videos.create_time,videos.img_url,videos.video_url,videos.flag FROM xks_study_videos AS videos, xks_tags AS tags   WHERE videos.flag != 0 AND  videos.title LIKE concat('%',?, '%') AND tags.tag_type = "学习视频" AND videos.id=tags.c_id AND tags.tag_name = ? limit ?,?`;
            sqlArr = [title, title, tag, tag, start, size]
        }
        db.query(sql, sqlArr, (err, results) => {
            if (err) {
                return res.send({
                    code: 505,
                    msg: '错误',
                    videos: {}
                })
            }
            // 计算总页数
            var allCount = results[0][0]['COUNT(*)'];
            // console.log('results[0],', results[0])
            var allPage = parseInt(allCount) / size;
            var pageStr = allPage.toString();
            // 不能被整除
            if (pageStr.indexOf('.') > 0) {
                allPage = parseInt(pageStr.split('.')[0]) + 1;
            }
            var list = results[1];
            return res.send({
                code: 200,
                msg: '成功',
                totalPages: allPage,
                total: allCount,
                currentPage: start + 1,
                videos: list
            })
        })
    }
}

/**
 * 增加学习视频
 * @param {*} req 
 * @param {*} res 
 * @returns 
 */
exports.addStudyVideos = (req, res) => {
    const studyvideos = req.body;
    if (JSON.stringify(studyvideos) == "{}") {
        console.log('【增加书签发送错误】' + 'req.body ===> ', req.body)
        return res.send({
            code: 400,
            msg: '因发送的请求语法错误,服务器无法正常读取。',
            videos: {}
        })
    }
    else {
        if (!studyvideos.title || !studyvideos.tags || !studyvideos.video_url) {
            return res.send({
                code: 400,
                msg: '有必填字段未填，请重新操作。',
                videos: {}
            })
        }
    }

    const snid = new SnowflakeID({
        mid: +new Date()
    });
    // //生成uuid
    let uuid1 = snid.generate();
    studyvideos['id'] = uuid1
    //记录标签的值
    const tag_name = studyvideos.tags
    //把资料里的标签属性删了，不插入该表中
    delete studyvideos.tags

    //创建时间
    let nowTime = new Date()
    var formatTime = moment(nowTime).format('YYYY-MM-DD HH:mm')
    studyvideos['create_time'] = formatTime;

    const addvideosSql = `INSERT INTO xks_study_videos SET ? `
    db.query(addvideosSql, studyvideos, (err, result) => {
        if (err) {
            console.log(err.message)
            return res.send({
                code: 505,
                msg: err.message,
                videos: {}
            })
        }
        if (result.affectedRows !== 1) {
            return res.send({
                code: 500,
                msg: '增加失败，请稍后再试！',
                videos: {}
            })
        }
        const addTagsSql = `INSERT INTO xks_tags SET ?`
        //生成uuid
        let uuid2 = snid.generate();
        // 添加id
        let tag = {
            id: uuid2,
            c_id: uuid1,
            tag_name: tag_name,
            tag_type: '学习视频'
        }
        db.query(addTagsSql, tag, (err, result) => {
            if (err) {
                console.log(err.message)
                return res.send({
                    code: 505,
                    msg: err.message,
                    videos: {}
                })
            }
            // console.log('uuid1:' + uuid1)
            // console.log('uuid2:' + uuid2)

            console.log('增加学习视频', studyvideos.name)
            res.send({
                code: 200,
                msg: '增加成功',
                videos: {}
            })
        })
    })
}


/**
 * 删除学习视频
 * @param {*} req 
 * @param {*} res 
 * @returns 
 */
exports.delStudyvideos = (req, res) => {

    const studyvideos = req.query;
    if (JSON.stringify(studyvideos) == "{}") {
        console.log('【删除学习视频发送错误】' + 'req.body ===> ', req.body)
        return res.send({
            code: 400,
            msg: '因发送的请求语法错误,服务器无法正常读取。',
            videos: {}
        })
    }
    else {
        if (!studyvideos.id) {
            return res.send({
                code: 400,
                msg: '有必填字段未填，请重新操作。',
                videos: {}
            })
        }
    }
    const delSql = `UPDATE xks_study_videos SET flag = 0 WHERE id = ?`
    db.query(delSql, studyvideos.id, (err, result) => {
        if (err) {
            console.log(err.message)
            return res.send({
                code: 505,
                msg: err.message,
                videos: {}
            })
        }
        if (result.affectedRows !== 1) {
            return res.send({
                code: 500,
                msg: '删除失败，请检查ID是否有误！',
                videos: {}
            })
        }
        const delSql = `UPDATE xks_tags SET flag = 0 WHERE c_id = ? `
        db.query(delSql, studyvideos.id, (err, result) => {
            res.send({
                code: 200,
                msg: '删除成功！',
                videos: {}
            })
        })
    })

}


/**
 * 更新学习视频
 * @param {*} req 
 * @param {*} res 
 * @returns 
 */
exports.updateStudyVideos = (req, res) => {
    const studyvideos = req.body;
    if (JSON.stringify(studyvideos) == "{}") {
        console.log('【更新学习视频发送错误】' + 'req.body ===> ', req.body)
        return res.send({
            code: 400,
            msg: '因发送的请求语法错误,服务器无法正常读取。',
            videos: {}
        })
    }
    else {
        if (!studyvideos.title || !studyvideos.tags || !studyvideos.video_url || !studyvideos.id) {
            return res.send({
                code: 400,
                msg: '有必填字段未填，请重新操作。',
                videos: {}
            })
        }
    }

    //记录标签和id的值
    const tag_name = studyvideos.tags
    const videosId = studyvideos.id

    delete studyvideos.tags
    delete studyvideos.id

    const addvideosSql = `UPDATE xks_study_videos SET ? WHERE flag != 0 AND id = ?`
    db.query(addvideosSql, [studyvideos, videosId], (err, result) => {
        if (err) {
            console.log(err.message)
            return res.send({
                code: 505,
                msg: err.message,
                videos: {}
            })
        }
        if (result.affectedRows !== 1) {
            return res.send({
                code: 500,
                msg: '更新失败，请稍后再试！',
                videos: {}
            })
        }

        // console.log(tag_name, videosId)
        const updateTagSql = `UPDATE xks_tags SET tag_name = ? , c_id = ?  WHERE tag_type = '学习视频' AND c_id = ?`
        db.query(updateTagSql, [tag_name, videosId, videosId], (err, result) => {
            if (err) {
                console.log(err.message)
                return res.send({
                    code: 505,
                    msg: err.message,
                    videos: {}
                })
            }
            res.send({
                code: 200,
                msg: '更新成功',
                videos: {}
            })
        })
    })
}